package models;

import java.sql.ResultSet;
import java.sql.SQLException;

public class InStoreReturnModel extends AbstractModel {
	
	private int retId = -1;
	
	public boolean validateReceiptAndStore(int receiptId, String sname) {
		try {
			ps = con.prepareStatement("SELECT * FROM Purchase WHERE receiptId = ? AND purchaseDate >= (sysdate - 15)");
			ps.setInt(1, receiptId);
			ResultSet rs = ps.executeQuery();
			boolean isReceiptValid = rs.next();
			
			ps = con.prepareStatement("SELECT * FROM Store WHERE sname = ?");
			ps.setString(1, sname);
			rs = ps.executeQuery();
			
			return rs.next() && isReceiptValid;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return registerException(ex);
		}
	}
	
	public boolean validateReturnItem(int receiptId, int upc, int quantity)
	{
		try {
			ps = con.prepareStatement("SELECT quantity FROM PurchaseItem WHERE receiptId = ? AND upc = ?");
			ps.setInt(1, receiptId);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			
			int purchasedQuantity;
			
			if (rs.next())
			{
				purchasedQuantity = rs.getInt("quantity");
				if (rs.getInt("quantity") < quantity) return false;
				
				ps = con.prepareStatement("SELECT ReturnItem.quantity FROM ReturnTransaction, ReturnItem WHERE " +
						"ReturnTransaction.receiptId = ? AND ReturnItem.upc = ? AND ReturnTransaction.retid = ReturnItem.retid");
				ps.setInt(1, receiptId);
				ps.setInt(2, upc);
				rs = ps.executeQuery();
				int returnedQuantity = 0;
				
				while (rs.next()) returnedQuantity += rs.getInt("quantity");
				
				return (returnedQuantity + quantity <= purchasedQuantity);
			}
			else return false;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return registerException(ex);
		}
	}
	
	private int generateReturnId(int receiptId, String sname) {
		try {
			ps = con.prepareStatement("INSERT INTO ReturnTransaction VALUES (returnId_counter.nextval, sysdate, ?,?)");
			ps.setInt(1, receiptId);
			ps.setString(2, sname);
			
			ps.executeUpdate();
			ps = con.prepareStatement("SELECT returnId_counter.currval FROM dual");
			ResultSet rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1);
		} catch(SQLException ex) { ex.printStackTrace(); return -1; }
	}
	
	public boolean processReturn(int receiptId, String sname, int upc, int quantity) {
		if (retId == -1) retId = generateReturnId(receiptId, sname);
		try {
			ps = con.prepareStatement("SELECT stock FROM Stored WHERE sname = ? AND upc = ? ");
			ps.setString(1, sname);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			rs.next();
			int newStock = rs.getInt("stock") + quantity;
			
			ps = con.prepareStatement("UPDATE Stored SET stock = ? WHERE sname = ? AND upc = ? ");
			ps.setInt(1, newStock);
			ps.setString(2, sname);
			ps.setInt(3, upc);
			ps.executeUpdate();
			
			ps = con.prepareStatement("INSERT INTO ReturnItem VALUES (?,?,?)");
			ps.setInt(1, retId);
			ps.setInt(2, upc);
			ps.setInt(3, quantity);
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			try { con.rollback(); }
			catch (SQLException ex1) { ex1.printStackTrace(); registerException(ex1); }
			return registerException(ex);
		}
		return true;
	}
	
	public boolean commit()
	{
		try {
			con.commit();
			retId = -1;
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return registerException(ex);
		}
	}

	@Override
	public ResultSet show() {
		// TODO Auto-generated method stub
		return null;
	}

}
